/*
 * Created on Sep 25, 2003
 * ApproveDB
 * Local Projects Database
 * (comparator) 2003 eRomania Gateway Association
 *
 */
package ro.gateway.aida.usr.messaging;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.GenericActivityProperty;
import ro.gateway.aida.obj.db.GenericActivityPropertyDB;
import ro.gateway.aida.utils.SortedList;


/**
 * @author Mihai Postelnicu ApproveDB
 *         <p/>
 *         *
 */
public class ApproveDB extends DBPersistenceManager {
     private ApproveDB( PersistenceToken token ) {
          super( token );
     }

     public static ApproveDB getManager( PersistenceToken token ) {
          return new ApproveDB( token );
     }

     public boolean setApproveActivity( boolean approve, long activity_id )
        throws SQLException {
          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( SET_APPROVE_PROJECT );
               ps.setString( 1, bool2Str( approve ) );
               ps.setLong( 2, activity_id );
               ps.executeUpdate();
               ps.close();

               return true;
          } finally {
               con.close();
          }
     }

     public boolean unifyApproveState( boolean approve, long event_id )
        throws SQLException {
          Connection con = getConnection();

          try {
               PreparedStatement ps = con.prepareStatement( UNIFY_APPROVE_STATE );
               ps.setString( 1, bool2Str( approve ) );
               ps.setLong( 2, event_id );
               ps.executeUpdate();
               ps.close();

               return true;
          } finally {
               con.close();
          }
     }

//TODO:implement arbiter exit
     public long add( Approve ap ) throws SQLException {
		  Alert a = ( Alert ) ap;
		  AlertDB adb = AlertDB.getManager( token );
		  long id = adb.add( a );
          Connection con = getConnection();
       	  if(id==-1) return -1;  
		
          try {
            PreparedStatement ps = con.prepareStatement( INSERT_APPROVE );
            ps.setLong( 1, id );
            ps.setString( 2, ap.getReason() );
            ps.setString( 3, bool2Str( ap.isApproved() ) );
            ps.setInt( 4, ap.getFeedback() );
            ps.setLong( 5, ap.getTo() );
            ps.executeUpdate();
            ps.close();
		
           return id;
          } finally {
               con.close();
          }
     }

     public boolean update( Approve ap ) throws SQLException {
          Connection con = getConnection();
          AlertDB adb = AlertDB.getManager( token );
          adb.update( ap );

          try {
               PreparedStatement ps = con.prepareStatement( UPDATE_APPROVAL );
               ps.setString( 1, ap.getReason() );
               ps.setString( 2, bool2Str( ap.isApproved() ) );
               ps.setInt( 3, ap.getFeedback() );
               ps.setLong( 4, ap.getTo() );
               ps.setLong( 5, ap.getEventId() );
               ps.executeUpdate();
               ps.close();

               return true;
          } catch ( SQLException e ) {
               return false;
          } finally {
               try {
                    con.close();
               } catch ( SQLException sqlEx ) {
               }
          }
     }

     public GenericActivityProperty appendTitle( long activity_id, String lang ) {
          GenericActivityProperty gap = new GenericActivityProperty();

          if ( "en".equals( lang ) ) {
               gap.setName( "en",
                  "Project number " + activity_id +
                  "  <b>[No project title defined!]</b>" );
          }

          if ( "ro".equals( lang ) ) {
               gap.setName( "ro",
                  "Project numarul " + activity_id +
                  "  <b>[No a fost definit un titlu!]</b>" );
          }

          //TODO:IMPLEMENT MULTIPLE LNG SUPPORT
          try {
               gap = GenericActivityPropertyDB.getManager( token )
                  .getFirstForActivity( "gen_titles",
                     activity_id );
          } catch ( SQLException e ) {
               e.printStackTrace();
          }

          return gap;
     }

     public Approve getByIds( int user_id, int log_id, String lang )
        throws SQLException {
          Connection con = getConnection();
          AlertDB adb = AlertDB.getManager( token );
          Alert a = adb.getByIds( user_id, log_id );
          Approve ap = ( Approve ) a;

          try {
               PreparedStatement ps = con.prepareStatement( GET_APPROVE );
               ps.setInt( 1, user_id );
               ps.setInt( 2, log_id );

               ResultSet rs = ps.executeQuery();

               if ( rs.next() ) {
                    ap.setReason( rs.getString( 1 ) );
                    ap.setApproved( str2Bool( rs.getString( 2 ) ) );
                    ap.setFeedback( rs.getInt( 3 ) );
                    ap.setTitle( appendTitle( a.getSourceObjId(), lang ) );
                    ap.setLang( lang );
               }

               rs.close();
               ps.close();

               return ap;
          } finally {
               con.close();
          }
     }

     public SortedList getForUser( long user_id, String lang, int type )
        throws SQLException {
          Connection con = getConnection();
          SortedList v = new SortedList();
          String filter = "";

          switch ( type ) {
               case Approve.APPROVED:
                    filter = APPROVED_REQUESTS;

                    break;

               case Approve.REJECTED:
                    filter = REJECTED_REQUESTS;

                    break;

               case Approve.UNAPPROVED:
                    filter = UNAPPROVED_REQUESTS;

                    break;
          }

          try {
               PreparedStatement ps = con.prepareStatement( GET_FOR_USER_APPROVALS_ALL +
                  filter );
               ps.setLong( 1, user_id );

               ResultSet rs = ps.executeQuery();

               while ( rs.next() ) {
                    Approve a = new Approve();
                    a.setPriority( rs.getInt( 1 ) );
                    a.setByEmail( str2Bool( rs.getString( 2 ) ) );
                    a.setEventType( rs.getInt( 3 ) );
                    a.setDeleted( str2Bool( rs.getString( 4 ) ) );
                    a.setRead( rs.getDate( 5 ) );
                    a.setEventId( rs.getInt( 6 ) );
                    a.setSourceObjId( rs.getLong( 7 ) );
                    a.setSourceUserId( rs.getInt( 8 ) );
                    a.setSourceType( rs.getInt( 9 ) );
                    a.setEventType( rs.getInt( 10 ) );
                    a.setSent( rs.getDate( 11 ) );
                    a.setReason( rs.getString( 12 ) );
                    a.setApproved( str2Bool( rs.getString( 13 ) ) );
                    a.setFeedback( rs.getInt( 14 ) );
                    a.setTo( rs.getInt( 15 ) );
                    a.setSourceUserName( rs.getString( 16 ) + " " + rs.getString( 17 ) );
                    a.getParams().put( "user_id", new Long( user_id ) );
                    a.getParams().put( "event_id", new Long( a.getEventId() ) );
                    a.getParams().put( "id", new Long( a.getSourceObjId() ) );
                    a.setTitle( appendTitle( a.getSourceObjId(), lang ) );
                    a.setLang( lang );
                    v.add( a );
               }

               rs.close();
               ps.close();

               return v;
          } finally {
               con.close();
          }
     }

     public static final String GET_FOR_USER_APPROVALS_ALL =
        "SELECT al.priority,al.by_email,al.alert_type,al.deleted,al.read,al.event_id," +
        "l.source_obj_id,l.source_user_id,l.source_type,l.event_type,l.sent,ap.reason, ap.approved, ap.feedback," +
        "al.user_id,u.first_names,u.last_name FROM alerts al, events l, approvals ap,users u " +
        "WHERE u.id=l.source_user_id AND l.id=al.event_id AND l.id=ap.event_id AND al.user_id=? AND ap.user_id=al.user_id";
     public static final String APPROVED_REQUESTS = " AND ap.approved='Y'";
     public static final String REJECTED_REQUESTS = " AND ap.approved='N'";
     public static final String UNAPPROVED_REQUESTS = " AND ap.approved IS NULL";
     public static final String GET_APPROVE = "SELECT reason,approved,feedback" +
        " FROM approvals WHERE user_id=? AND event_id=?";
     public static final String INSERT_APPROVE = "INSERT INTO approvals " +
        "(event_id,reason,approved,feedback,user_id) VALUES (?,?,?,?,?)";
     public static final String SET_APPROVE_PROJECT = "UPDATE activities SET approved=? WHERE id=?";
     public static final String UNIFY_APPROVE_STATE = "UPDATE approvals SET approved=? WHERE event_id=?";
     public static final String UPDATE_APPROVAL = "UPDATE approvals SET reason=?,approved=?,feedback=? WHERE user_id=? AND event_id=?";
}
